package com.szholly.pro.watergas_server.ps.android.controller;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.ServletContext;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.szholly.data.common.util.ConvertCaptionUtils;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.RowBase;
import com.szholly.data.general.interceptor.DbEditType;
import com.szholly.plug.form.core.interceptor.FormEditInterceptorClass;
import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.common.util.RedisHelper;
import com.szholly.pro.watergas_server.ps.service.OnlineMonitorService;
import com.szholly.pro.watergas_server.ps.service.RemoteService;
import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.utils.util.JSONUtils;
import com.szholly.utils.util.StringUtils;
import org.apache.commons.lang3.ObjectUtils;
import org.codehaus.jackson.map.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.context.ServletContextAware;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

@Controller
@RequestMapping("/android/movedata/*")
public class AndroidAddApi implements ServletContextAware {
	private DataModel dataModel;
	@Autowired
	OnlineMonitorService onlineMonitorService;
	@Autowired
	RemoteService remoteService;
	// Spring这里是通过实现ServletContextAware接口来注入ServletContext对象
	private ServletContext servletContext;
	private String token = "android_token";

	// 获取版本号
	@RequestMapping("/updateno")
	@ResponseBody
	public String getVersionNo(String type) {
		List<GlobalParameter> parms = new ArrayList();
		String sql = null;
		if(null == type || type.isEmpty()){
			sql = "select * from ANDROID_VERSION_CONFIG t order by Client_type asc ";
		}else{
			sql = "select * from ANDROID_VERSION_CONFIG t where t.client_type = @type ";
		}	
		parms.add(new GlobalParameter("type", type));
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (rows != null && rows.size() >= 0) {
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}
	}

	/**
	 * 更新 wzj
	 * @throws Exception
	 */
	@RequestMapping("/update")
	@ResponseBody
	// 提供更新APK接口
	public void upteApp() {

		// // 获取网站部署路径(通过ServletContext对象)，用于确定下载文件位置，从而实现下载
		// String path = servletContext.getRealPath("/");
		// // 1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
		// response.setContentType("multipart/form-data");
		// // 2.设置文件头：最后一个参数是设置下载文件名(假如我们叫a.pdf)
		// response.setHeader("Content-Disposition", "attachment;fileName=" +
		// "MDP.apk");
		// ServletOutputStream out;
		// // 通过文件路径获得File对象(假如此路径中有一个download.pdf文件)
		// File file = new File("D:/360Downloads" + "/MDP.apk");
		// try {
		// FileInputStream inputStream = new FileInputStream(file);
		//
		// // 3.通过response获取ServletOutputStream对象(out)
		// out = response.getOutputStream();
		//
		// int b = 0;
		// byte[] buffer = new byte[512];
		// while ((b = inputStream.read(buffer)) > 0) {
		// // 4.写到输出流(out)中
		// out.write(buffer, 0, b);
		// }
		// inputStream.close();
		// out.flush();
		//
		// } catch (IOException e) {
		// e.printStackTrace();
		// }

	}

	/**
	 * 添加我的关注信息
	 * 
	 * @param userId
	 * @param stationId
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("/addAttention")
	@ResponseBody
	public String addAttention(String userId, String stationId) throws Exception {
		if (userId.isEmpty() || stationId.isEmpty()) {
			return "";
		}
		List<GlobalParameter> parms = new ArrayList();
		String selectSql = "select count(*) as num from ATTENTION_INFO where station_code = @STATION_CODE and user_id =@USER_ID ";
		String Sql = "insert into ATTENTION_INFO(GUID,STATION_CODE,USER_ID,CT,INFO) values (@GUID,@STATION_CODE,@USER_ID,@CT,@INFO)";
		String GUID = UUID.randomUUID().toString();
		parms.add(new GlobalParameter("GUID", GUID));
		parms.add(new GlobalParameter("STATION_CODE", stationId));
		parms.add(new GlobalParameter("USER_ID", userId));
		Date date = new Date();
		DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String time = format.format(date);

		parms.add(new GlobalParameter("CT", date));
		parms.add(new GlobalParameter("INFO", ""));
		List<RowBase> rows = DataUtil.getSelectRows(selectSql, parms);
		JSONArray list = JSONUtils.toJSONArray(rows);
		String num = list.getJSONObject(0).get("NUM").toString();
		if (num.equals("0")) {
			int i = DataUtil.ExecuteUpdateSql(Sql, parms);
			if (i == 0) {
				return "{\"result\":true}";
			} else {
				return "{\"result\":false}";
			}
		} else {
			return "{\"result\":true}";
		}

	}

	/**
	 * 获取我的关注站点信息
	 * 
	 * @param userId
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("/getAttention")
	@ResponseBody
	public String getAttention(String userId) throws Exception {
		if (userId.isEmpty()) {
			return "";
		}
		List<GlobalParameter> parms = new ArrayList();
		String Sql = "select t3.x,t3.y,t2.name,t.station_code from ATTENTION_INFO t left join BC_SITE_INFO t2 "
				+ "on t2.EXP_NO = t.station_code left join STATION_INFO t3 on  t2.EXP_NO = t3.station_code  "
				+ "where USER_ID = @userID ";
		parms.add(new GlobalParameter("userID", userId));
		List<RowBase> rows = DataUtil.getSelectRows(Sql, parms);
		if (rows != null && rows.size() >= 0) {
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}
	}

	/**
	 * 删除我的关注站点信息
	 * 
	 * @param userId
	 * @param stationId
	 * @return
	 * @throws Exception
	 */
	@RequestMapping("/delAttention")
	@ResponseBody
	public String delAttention(String userId, String stationId) throws Exception {
		if (userId.isEmpty() || stationId.isEmpty()) {
			return "";
		}
		List<GlobalParameter> parms = new ArrayList();
		String Sql = "delete from ATTENTION_INFO t where t.user_id = @userID and t.station_code = @stationId";
		parms.add(new GlobalParameter("userID", userId));
		parms.add(new GlobalParameter("stationId", stationId));
		int i = DataUtil.ExecuteUpdateSql(Sql, parms);
		if (i == 0) {
			return "{\"result\":true}";
		} else {
			return "{\"result\":false}";
		}
	}

	/**
	 * 获取地图中实时数据信息
	 * 
	 * @param @throws
	 */
	@RequestMapping("/getMapRunData")
	@ResponseBody
	public String getMapRunData(String stationCode) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select to_char(t.DATATIME,'yyyy-mm-dd hh24:mi:ss')as DATATIME,sum(decode(t.PARMNAME, '011',RTD,null)) as CODcr,sum(decode(t.PARMNAME, '060',"
				+ " RTD,null)) as 氨氮,sum(decode(t.PARMNAME, '101', RTD,null)) as 总磷,sum(decode(t.PARMNAME, '001', "
				+ "RTD,null)) as pH from monitor_data t left join BC_COLLECTION_INFO t2 on t.mn"
				+ "=t2.device_num where t2.site_id='" + stationCode + "' and  rownum <= 40 group by t.DATATIME order "
				+ "by t.DATATIME desc ";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (rows != null && rows.size() >= 0) {
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}
	}

	/**
	 * 获取所有站点信息
	 * 
	 * @param @throws
	 */
	@RequestMapping("/getInitInfo")
	@ResponseBody
	public String getInitInfo() {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select * from MONITOR_STATION";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		String sql1 = "select * from BC_ENTERPRISE_ATTR";
		List<RowBase> rows1 = DataUtil.getSelectRows(sql1, parms);
		String sql2 = "select * from OPERATION_GROUP_STATION_ASSO";
		List<RowBase> rows2 = DataUtil.getSelectRows(sql2, parms);
		String sql3 = "select * from BC_SITE_POLL ";
		List<RowBase> rows3 = DataUtil.getSelectRows(sql3, parms);
		String sql4 = "select S_DOMAINCODE,S_DOMAINCAPTION from data_defulatdomain where S_DOMAINNAME='MONITOR_PARAM'";
		List<RowBase> rows4 = DataUtil.getSelectRows(sql4, parms);
		JSONArray jsonObj = new JSONArray();
		JSONObject chidlobject = new JSONObject();
		chidlobject.put("STATIONINFO", rows);
		chidlobject.put("SCINFO", rows1);
		chidlobject.put("ACCESSINFO", rows2);
		chidlobject.put("UPDOWN", rows3);
		chidlobject.put("MONITOR_PARAM", rows4);
		jsonObj.add(chidlobject);
		if (chidlobject != null && chidlobject.size() >= 0) {
			return JSONUtils.toJSONString(chidlobject);
		} else {
			return "";
		}
	}

	/**
	 * 获取人员信息
	 * 
	 * @param @throws
	 */
	@RequestMapping("/getAccessInfo")
	@ResponseBody
	public String getAccessInfo() {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select * from OPERATION_GROUP_STATION_ASSO";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (rows != null && rows.size() >= 0) {
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}
	}

	/**
	 * 获取地图中报警信息
	 * 
	 * @param @throws
	 */
	@RequestMapping("/getMapwarnData")
	@ResponseBody
	public String getMapwarnData(String stationCode) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select d2.s_domaincaption as paramname,t1.param,d1.s_domaincaption as typename,t1.type,d3.name,"
				+ "t1.station_code,t1.alarm_value from OP_ALAR_INFO t1 "
				+ "left join (select S_DOMAINCODE,S_DOMAINCAPTION from data_defulatdomain d where S_DOMAINNAME='报警类型') "
				+ "d1 on t1.type=d1.s_domaincode left join (select S_DOMAINCODE,S_DOMAINCAPTION from data_defulatdomain d "
				+ "where S_DOMAINNAME='MONITOR_PARAM') d2 on t1.param=d2.s_domaincode  left join "
				+ "(select name,exp_no from BC_SITE_INFO d )d3 on d3.exp_no = t1.station_code where  "
				+ "to_char(t1.start_time,'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd') ";
		if (!stationCode.isEmpty()) {
			sql += " and t1.station_code=@stationCode";
			parms.add(new GlobalParameter("stationCode", stationCode));
		}
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (rows != null && rows.size() >= 0) {
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}
	}

	/**
	 * 获取地图中站点信息
	 * 
	 * @param @throws
	 */
	@RequestMapping("/getStationInfo")
	@ResponseBody
	public String getStationInfo(String stationCode) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select  ou.name,ou.address, wwtp.exp_no,wwtp.OPERATOR,ou.legal_person,ou.mobile_phone from "
				+ "BC_ENTERPRISE_ATTR wwtp left join OPERATIONS_UNIT ou ON ou.guid = wwtp.OPERATOR where 1=1";
		if (!stationCode.isEmpty()) {
			sql += " and wwtp.exp_no=@stationCode";
			parms.add(new GlobalParameter("stationCode", stationCode));
		}
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (rows != null && rows.size() >= 0) {
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}
	}

	@Override
	public void setServletContext(ServletContext arg0) {
		// TODO Auto-generated method stub
		this.servletContext = servletContext;
	}

	/**
	 * 参数对应字典值
	 */
	@RequestMapping("/getparameterId")
	@ResponseBody
	public String getparameterId() {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select S_DOMAINCODE,S_DOMAINCAPTION from data_defulatdomain where S_DOMAINNAME='MONITOR_PARAM'";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (rows != null && rows.size() >= 0) {
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}

	}

	/**
	 * 获取实时数据
	 */
	@RequestMapping("/getruntimedata")
	@ResponseBody
	public String getRunTimeData(String scId, String kindNo) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select * from(select * from(select  t1.* from monitor_data t1 left join "
				+ "BC_COLLECTION_INFO t2 on t1.mn=t2.device_num where " + "t2.site_id='" + scId
				+ "') where PARMNAME = '" + kindNo + "' order by CREATETIME desc)where rownum <= 10";

		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (rows != null && rows.size() >= 0) {
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}

	}

	/**
	 * 获取站点名称和坐标点
	 * 
	 * @return
	 */
	@RequestMapping("/getAllName")
	@ResponseBody
	public String getAllName(String userId) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select g.GUID as status, f.station_code,t.name,f.x,f.y,t.FLOW_TYPE,t.wwtp_id as  factoryid,h.name as factoryname from  monitor_station t "
				+ "left join STATION_INFO f on f.station_code = t.exp_no  left join "
				+ "(select station_code,GUID from ATTENTION_INFO where  user_id = @userId) g on g.station_code = t.exp_no  "
				+ "left join BC_ENTERPRISE_ATTR h on t.wwtp_id = h.exp_no where t.STATION_CATEGORY='0'";
		List<RowBase> rows;
		parms.add(new GlobalParameter("userId", userId));
		try {
			rows = DataUtil.getSelectRows(sql, parms);
		} catch (Exception e) {
			return "";
		}

		if (rows != null && rows.size() >= 0) {
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}
	}

	/**
	 * 获取报警类型字典对比
	 * 
	 */
	@RequestMapping("/getWarnDataParam")
	@ResponseBody
	public String getWarnDataParam() {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = null;

		sql = "select S_DOMAINCODE,S_DOMAINCAPTION from data_defulatdomain where S_DOMAINNAME='报警类型'";

		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		JSONArray array = JSONArray.fromObject(rows);
		if (rows != null && rows.size() >= 0) {
			Object[] array1 = rows.toArray();
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}
	}

	/**
	 * 获取报警信息
	 * 
	 */
	@RequestMapping("/getWarnData")
	@ResponseBody
	public String getWarn(String stationCode, String Type, String PARAM, String startTime, String endTime) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = null;
		if (PARAM.isEmpty()) {
			if (startTime.isEmpty() || endTime.isEmpty()) {
				if (Type.isEmpty() && stationCode.isEmpty()) {
					Date dt = new Date();
					SimpleDateFormat matter1 = new SimpleDateFormat("yyyy-MM-dd");
					String data = matter1.format(dt);
					sql = "select * from OP_ALAR_INFO where START_TIME >= to_date('2016-05-28 0:00:00','yyyy-mm-dd hh24:mi:ss')";
				} else if (Type.isEmpty() && stationCode.isEmpty() == false) {
					sql = "select * from OP_ALAR_INFO where station_code = '" + stationCode + "'";
				} else if (Type.isEmpty() == false && stationCode.isEmpty()) {
					sql = "select * from OP_ALAR_INFO where TYPE = '" + Type + "'";
				} else {
					sql = "select * from OP_ALAR_INFO where station_code = '" + stationCode + "' and TYPE='"
							+ Type + "'";
				}
			} else {
				if (Type.isEmpty() && stationCode.isEmpty()) {
					Date dt = new Date();
					SimpleDateFormat matter1 = new SimpleDateFormat("yyyy-MM-dd");
					String data = matter1.format(dt);
					sql = "select * from OP_ALAR_INFO where START_TIME >= to_date('" + startTime
							+ "','yyyy-mm-dd hh24:mi:ss') and START_TIME <= to_date('" + endTime
							+ "','yyyy-mm-dd hh24:mi:ss')";
				} else if (Type.isEmpty() && stationCode.isEmpty() == false) {
					sql = "select * from OP_ALAR_INFO where station_code = '" + stationCode
							+ "'and START_TIME >= to_date('" + startTime
							+ "','yyyy-mm-dd hh24:mi:ss') and START_TIME <= to_date('" + endTime
							+ "','yyyy-mm-dd hh24:mi:ss')";
				} else if (Type.isEmpty() == false && stationCode.isEmpty()) {
					sql = "select * from OP_ALAR_INFO where TYPE = '" + Type + "'and START_TIME >= to_date('"
							+ startTime + "','yyyy-mm-dd hh24:mi:ss') and START_TIME <= to_date('" + endTime
							+ "','yyyy-mm-dd hh24:mi:ss')";
				} else {
					sql = "select * from OP_ALAR_INFO where station_code = '" + stationCode + "' and TYPE='"
							+ Type + "'and START_TIME >= to_date('" + startTime
							+ "','yyyy-mm-dd hh24:mi:ss') and START_TIME <= to_date('" + endTime
							+ "','yyyy-mm-dd hh24:mi:ss')";
				}
			}
		} else {
			if (startTime.isEmpty() || endTime.isEmpty()) {
				if (Type.isEmpty() && stationCode.isEmpty()) {
					Date dt = new Date();
					SimpleDateFormat matter1 = new SimpleDateFormat("yyyy-MM-dd");
					String data = matter1.format(dt);
					sql = "select * from OP_ALAR_INFO where START_TIME >= to_date('2016-05-28 0:00:00',"
							+ "'yyyy-mm-dd hh24:mi:ss') and PARAM = '" + PARAM + "'";
				} else if (Type.isEmpty() && stationCode.isEmpty() == false) {
					sql = "select * from OP_ALAR_INFO where station_code = '" + stationCode + "'and PARAM = '"
							+ PARAM + "'";
				} else if (Type.isEmpty() == false && stationCode.isEmpty()) {
					sql = "select * from OP_ALAR_INFO where TYPE = '" + Type + "'";
				} else {
					sql = "select * from OP_ALAR_INFO where station_code = '" + stationCode + "' and TYPE='"
							+ Type + "'and PARAM = '" + PARAM + "'";
				}
			} else {
				if (Type.isEmpty() && stationCode.isEmpty()) {
					Date dt = new Date();
					SimpleDateFormat matter1 = new SimpleDateFormat("yyyy-MM-dd");
					String data = matter1.format(dt);
					sql = "select * from OP_ALAR_INFO where START_TIME >= to_date('" + startTime
							+ "','yyyy-mm-dd hh24:mi:ss') and START_TIME <= to_date('" + endTime
							+ "','yyyy-mm-dd hh24:mi:ss')and PARAM = '" + PARAM + "'";
				} else if (Type.isEmpty() && stationCode.isEmpty() == false) {
					sql = "select * from OP_ALAR_INFO where station_code = '" + stationCode + "'and START_TIME "
							+ ">= to_date('" + startTime + "','yyyy-mm-dd hh24:mi:ss') and START_TIME <= to_date('"
							+ endTime + "','yyyy-mm-dd hh24:mi:ss')and PARAM = '" + PARAM + "'";
				} else if (Type.isEmpty() == false && stationCode.isEmpty()) {
					sql = "select * from OP_ALAR_INFO where TYPE = '" + Type + "'and START_TIME >= to_date('"
							+ startTime + "','yyyy-mm-dd hh24:mi:ss') and START_TIME <= to_date('" + endTime
							+ "','yyyy-mm-dd hh24:mi:ss')and PARAM = '" + PARAM + "'";
				} else {
					sql = "select * from OP_ALAR_INFO where station_code = '" + stationCode + "' and TYPE='"
							+ Type + "'and START_TIME >= to_date('" + startTime
							+ "','yyyy-mm-dd hh24:mi:ss') and START_TIME <= to_date('" + endTime
							+ "','yyyy-mm-dd hh24:mi:ss')and PARAM = '" + PARAM + "'";
				}
			}
		}

		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		JSONArray array = JSONArray.fromObject(rows);
		if (rows != null && rows.size() >= 0) {
			Object[] array1 = rows.toArray();
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}
	}

	/**
	 * 获取站点污染物取值范围
	 * 
	 * @throws Exception
	 */
	@RequestMapping(value = "/getWryDataByExpNo")
	@ResponseBody
	public String getWryDataByExpNo(String stationId) throws Exception {
		// String wwtp_id = RequestUnit.getString("WWTP_ID");
		// String station_id = RequestUnit.getString("STATION_ID");
		// String deviceId = RequestUnit.getString("deviceId");
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String Sql = "select t1.s_domaincaption,t.name,t.EMISSION_STANDARD,t.DENSITY_ALARM_DOWMLIMIT,t.DENSITY_ALARM_UPLIMIT,t.ABNORMAL_MIN,t.ABNORMAL_MAX from BC_SITE_POLL t "
				+ "left join (select S_DOMAINCODE,S_DOMAINCAPTION from data_defulatdomain where S_DOMAINNAME='MONITOR_PARAM') t1 on t.name = t1.s_domaincode where 1=1 ";

		if (!stationId.isEmpty()) {
			Sql += " and t.site_id=@stationId";
			parms.add(new GlobalParameter("stationId", stationId));
		}

		return "";
	}

	/**
	 * 获取历史数据具体
	 * 
	 * 
	 */
	@RequestMapping("/getHisData")
	@ResponseBody
	public String getHisData(String startTime, String endTime, String PARMNAME, String site_id) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = null;
		if (startTime.isEmpty() || endTime.isEmpty()) {
			sql = "select RTD,to_char(DATATIME,'yy-mm-dd hh24:mi:ss')as DATATIME from (select * from monitor_data t left join  BC_COLLECTION_INFO t1 on  t.MN = t1.DEVICE_NUM where 1=1";
			if (!site_id.isEmpty()) {
				sql += " and t1.site_id =@site_id";
				parms.add(new GlobalParameter("site_id", site_id));
			}
			if (!PARMNAME.isEmpty()) {
				sql += " and t.PARMNAME=@PARMNAME";
				parms.add(new GlobalParameter("PARMNAME", PARMNAME));
			}
			sql += "order by t.DATATIME desc )where rownum <= 50";
		} else {
			sql = "select * from(select to_char(DATATIME,'yy-mm-dd hh24:mi:ss')as DATATIME,RTD from monitor_data t left join  BC_COLLECTION_INFO t1 on t.MN = t1.DEVICE_NUM where 1=1 "
					+ "and t.DATATIME>=to_date('" + startTime + "','yyyy-MM-dd HH24:MI:SS') and t.DATATIME <= to_date('"
					+ endTime + "','yyyy-MM-dd HH24:MI:SS')and   " + "t1.site_id ='" + site_id + "' and t.PARMNAME='"
					+ PARMNAME + "' order by t.DATATIME desc)where rownum <= 20 ";

			// sql = "select RTD,createtime from monitor_data t where
			// DATATIME>=to_date('" + startTime + "','yyyy-MM-dd HH24:MI:SS') "
			// + "and DATATIME <= to_date('" + endTime + "','yyyy-MM-dd
			// HH24:MI:SS') and PARMNAME='" + PARMNAME + "'order by createtime
			// desc";
		}
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		JSONArray array = JSONArray.fromObject(rows);
		if (rows != null && rows.size() >= 0) {
			Object[] array1 = rows.toArray();
			return JSONUtils.toJSONString(rows);
		} else {
			return "";
		}
	}

	/**
	 * 获取报警信息 2016-7-8
	 */
	@RequestMapping("/getAllWarnData")
	@ResponseBody
	public String getAllWarnData(String userId) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = null;
		if (!userId.isEmpty()) {
			sql = "select t3.S_DOMAINCAPTION as typename,t2.S_DOMAINCAPTION as paramname, t1.guid as actionstatus,t.type,t.param,to_char(t.start_time,'yyyy-mm-dd HH24:MI:SS')as start_time,"
					+ " t.alarm_value,t.station_code,t5.wwtp_id,t5.name,t.status,t.continue_time,t.is_recover,to_char(t.recover_time,'yyyy-mm-dd HH24:MI:SS')as recover_time from "
					+ " OP_ALAR_INFO t  "
					+ " left join (select S_DOMAINCODE,S_DOMAINCAPTION from data_defulatdomain "
					+ " where S_DOMAINNAME='MONITOR_PARAM')t2 on t2.S_DOMAINCODE = t.param  "
					+ " left join BC_SITE_INFO t5 on t.station_code = t5.exp_no"
					+ " left join (select guid,station_code from ATTENTION_INFO where USER_ID = @userId ) t1 on t1.station_code = t5.wwtp_id  "
					+ " left join (select S_DOMAINCODE,S_DOMAINCAPTION from data_defulatdomain where S_DOMAINNAME='报警类型')t3 on t3.S_DOMAINCODE = t.type "
					+ " where t5.station_category = '0' and t.is_recover ='0'";

			parms.add(new GlobalParameter("userId", userId));
			List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
			JSONArray array = JSONArray.fromObject(rows);
			if (rows != null && rows.size() >= 0) {
				Object[] array1 = rows.toArray();
				return JSONUtils.toJSONString(rows);
			} else {
				return "";
			}
		} else {
			return "";
		}

	}

	/**
	 * 获取小时所有历史数据 2016-7-12
	 * 
	 */
	@RequestMapping("/getHourHisData")
	@ResponseBody
	public String getHourHisData(String stationId, String dataTime,String date ) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = null;
		if (!stationId.isEmpty() && !dataTime.isEmpty()) {
			if(date == null ){
				sql = "select t2.s_domaincaption,t.parmname,t1.id,t.cou,t.max,t.min,t.avg,"
						+ "to_char(t.datatime,'yyyy-mm-dd hh24:mi:ss')as datetime from monitor_data_hour t "
						+ " left join BC_COLLECTION_INFO t1 on t.mn = t1.device_num  "
						+ "left join data_defulatdomain  t2 "
						+ "on t.parmname = t2.s_domaincode where t1.site_id = @stationId and  t2.S_DOMAINNAME='MONITOR_PARAM'"
						+ "and t.datatime >= to_date(@dataTime,'yyyy-mm-dd') and t.datatime < (to_date(@dataTime,'yyyy-mm-dd')+1) order by t.datatime asc";
			}else{
				sql = "select t2.s_domaincaption,t.parmname,t1.id,t.cou,t.max,t.min,t.avg,"
						+ "to_char(t.datatime,'yyyy-mm-dd hh24:mi:ss')as datetime from monitor_data_hour t "
						+ " left join BC_COLLECTION_INFO t1 on t.mn = t1.device_num  "
						+ "left join data_defulatdomain  t2 "
						+ "on t.parmname = t2.s_domaincode where t1.site_id = @stationId and  t2.S_DOMAINNAME='MONITOR_PARAM'"
						+ "and t.datatime >= (to_date('2017-03-03','yyyy-mm-dd') -6) and t.datatime < (to_date(@dataTime,'yyyy-mm-dd')+1) order by t.datatime asc";
			}
			parms.add(new GlobalParameter("stationId", stationId));
			parms.add(new GlobalParameter("dataTime", dataTime));
			List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
			JSONArray array = JSONArray.fromObject(rows);
			if (rows != null && rows.size() >= 0) {
				Object[] array1 = rows.toArray();
				return JSONUtils.toJSONString(rows);
			} else {
				return "";
			}
		} else {
			return "";
		}

	}

	/**
	 * 获取小时所有历史数据 2016-7-12
	 * 
	 */
	@RequestMapping("/getSevenHourHisData")
	@ResponseBody
	public String getSevenHourHisData(String stationId, String dataTime) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = null;
		if (!stationId.isEmpty() && !dataTime.isEmpty()) {
			sql = "select t2.s_domaincaption,t.parmname,t1.id,t.cou,t.max,t.min,t.avg,"
					+ "to_char(t.datatime,'yyyy-mm-dd hh24:mi:ss')as datetime from monitor_data_hour t "
					+ " left join BC_COLLECTION_INFO t1 on t.mn = t1.device_num  "
					+ "left join data_defulatdomain  t2 "
					+ "on t.parmname = t2.s_domaincode where t1.site_id = @stationId and  t2.S_DOMAINNAME='MONITOR_PARAM'"
					+ "and t.datatime >= (to_date('2017-03-03','yyyy-mm-dd') -6) and t.datatime < (to_date(@dataTime,'yyyy-mm-dd')+1) order by t.datatime asc";

			parms.add(new GlobalParameter("stationId", stationId));
			parms.add(new GlobalParameter("dataTime", dataTime));
			List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
			JSONArray array = JSONArray.fromObject(rows);
			if (rows != null && rows.size() >= 0) {
				Object[] array1 = rows.toArray();
				return JSONUtils.toJSONString(rows);
			} else {
				return "";
			}
		} else {
			return "";
		}

	}
	
	/**
	 * 获取小时某参数历史数据 2016-7-12
	 * 
	 */
	@RequestMapping("/getHourHisDataByparm")
	@ResponseBody
	public String getHourHisDataByparm(String stationId, String dataTime, String parm) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = null;
		if (!stationId.isEmpty() && !dataTime.isEmpty()) {
			sql = "select t2.s_unit,t2.s_domaincaption,t.parmname,t1.id,t.cou,t.max,t.min,t.avg,"
					+ "to_char(t.datatime,'yyyy-mm-dd hh24:mi:ss')as datetime from monitor_data_hour t "
					+ " left join BC_COLLECTION_INFO t1 on t.mn = t1.device_num  "
					+ "left join (select S_DOMAINCODE,S_DOMAINCAPTION,s_unit from data_defulatdomain where S_DOMAINNAME='MONITOR_PARAM') t2 "
					+ "on t.parmname = t2.s_domaincode where t1.site_id = @stationId "
					+ "and to_char(t.datatime,'yyyy-mm-dd')=@dataTime and  t.parmname =@parm order by t.datatime desc";

			parms.add(new GlobalParameter("stationId", stationId));
			parms.add(new GlobalParameter("dataTime", dataTime));
			parms.add(new GlobalParameter("parm", parm));
			List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
			JSONArray array = JSONArray.fromObject(rows);
			if (rows != null && rows.size() >= 0) {
				Object[] array1 = rows.toArray();
				return JSONUtils.toJSONString(rows);
			} else {
				return "";
			}
		} else {
			return "";
		}

	}

	/**
	 * 获取站点所属区域，报警线，站点位置，坐标，站点信息等 2016-7-16
	 * 
	 */
	@RequestMapping("/getStationAreaByuserId")
	@ResponseBody
	public String getStationAreaByuserId(String userId) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = null;
		if (!userId.isEmpty()) {
			sql = "select t3.guid as Attentionstatus,t.name as stationname,t1.s_domaincaption as unitname,t.exp_no as stationid,t.wwtp_id as factoryid,t.flow_type,t4.x,t4.y,t5.name as factoryname,t5.belong_dept,"
					+ "to_char(t5.rundate,'yyyy-mm-dd hh24:mi:ss')as datetime,t5.legal_person,t5.contact_address,t5.mobile,t8.S_DOMAINCODE,t8.S_DOMAINCAPTION as paramname,t8.EMISSION_STANDARD,t8.DENSITY_ALARM_DOWMLIMIT,t8.DENSITY_ALARM_UPLIMIT,"
					+ "t8.ABNORMAL_MIN, t8.ABNORMAL_MAX from BC_SITE_INFO t left join (select S_DOMAINCODE,S_DOMAINNAME,s_domaincaption,PY_PREFIX,"
					+ "s_id,s_pid ,TYPE from DATA_DEFULATDOMAIN  where length(s_id)='2' and S_DOMAINNAME ='政区')"
					+ " t1 on SUBSTR(t.exp_no,3,2) = t1.s_id  left join (select * "
					+ "from ATTENTION_INFO where user_id =@userId) "
					+ "t3 on t3.station_code = t.wwtp_id left join STATION_INFO t4  on t4.station_code = t.exp_no left "
					+ "join BC_ENTERPRISE_ATTR t5 on t5.exp_no =t.wwtp_id left join (select t7.S_DOMAINCODE,t7.S_DOMAINCAPTION,t6.site_id,t6.EMISSION_STANDARD,"
					+ "t6.DENSITY_ALARM_DOWMLIMIT,t6.DENSITY_ALARM_UPLIMIT,t6.ABNORMAL_MIN, t6.ABNORMAL_MAX from BC_SITE_POLL t6 "
					+ "left join (select S_DOMAINCODE,S_DOMAINCAPTION from data_defulatdomain where S_DOMAINNAME='MONITOR_PARAM') t7 "
					+ "on t6.name = t7.s_domaincode) t8 on t8.site_id = t.exp_no where t.station_category='0'"
					+ " and t4.x IS NOT NULL and t4.y IS NOT NULL"
					+ " order by t1.s_domaincaption desc";
			parms.add(new GlobalParameter("userId", userId));
			List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
			JSONArray array = JSONArray.fromObject(rows);
			if (rows != null && rows.size() >= 0) {
				Object[] array1 = rows.toArray();
				return JSONUtils.toJSONString(rows);
			} else {
				return "";
			}
		} else {
			return "";
		}

	}

	/**
	 * 获取站点最新数据及报警线 2016-7-15
	 * 
	 */
	@RequestMapping("/getNewStationData")
	@ResponseBody
	public String getAllwarnByDate(String stationId) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = null;
		if (!stationId.isEmpty()) {
			sql = "select t3.s_domaincaption,t.cou,t.max,t.min,t.avg,to_char(t.createtime,'yy-mm-dd hh24:mi:ss')as createtime,"
					+ "t.flag,t.error_data,t.Rtd,t4.EMISSION_STANDARD,t4.DENSITY_ALARM_DOWMLIMIT,t4.DENSITY_ALARM_UPLIMIT,t4.ABNORMAL_MIN,"
					+ "t4.ABNORMAL_MAX from monitor_data t left join BC_COLLECTION_INFO t2 on t.mn =t2.device_num  "
					+ "left join (select S_DOMAINCODE,S_DOMAINCAPTION from data_defulatdomain where S_DOMAINNAME='MONITOR_PARAM') t3 "
					+ "on t3.S_DOMAINCODE = t.parmname left join BC_SITE_POLL  t4 on t4.name= t3.s_domaincode  where  t2.site_id=@stationId "
					+ "and t.datatime =  (select * from (select t.datatime from monitor_data t left join BC_COLLECTION_INFO "
					+ "t2 on t.mn =t2.device_num where t2.site_id=@stationId order by datatime desc ) where rownum=1 ) and t4.site_id = @stationId";
			parms.add(new GlobalParameter("stationId", stationId));
			List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
			JSONArray array = JSONArray.fromObject(rows);
			if (rows != null && rows.size() >= 0) {
				Object[] array1 = rows.toArray();
				return JSONUtils.toJSONString(rows);
			} else {
				return "";
			}
		} else {
			return "";
		}

	}

	/**
	 * 获取设备是否在线, 0正常、1超标、2异常、3故障
	 */
	@RequestMapping("/getOnline")
	@ResponseBody
	public String getOnline() {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sqlmn = "select t1.device_num,site_id from BC_COLLECTION_INFO t1";
		List<RowBase> rowmn = DataUtil.getSelectRows(sqlmn, parms);
		JSONObject jsonObj = new JSONObject();
		if (rowmn.size() > 0) {
			for (int i = 0; i < rowmn.size(); i++) {
				String mn = rowmn.get(i).getSafeString("DEVICE_NUM");
				String status = RedisHelper.getInstance().getStationStatus(mn);
				jsonObj.put(rowmn.get(i).getSafeString("site_id"), status);
			}
		}

		return JSONUtils.toJSONString(jsonObj);
		// String status = RedisHelper.getInstance().getStationStatus(mn);
	}

	/**
	 * 获取实时数据
	 * 
	 * @param wwtp_id
	 *            污水厂id
	 */
	@RequestMapping("/getRealData")
	@ResponseBody
	public String getRealData(String wwtp_id) {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String mn_in = "";// 进水口数采仪编号
		String mn_out = "";// 出水口数采仪编号
		// 1、获取数采仪编码
		String sqlmn = "select t1.device_num,t2.name,t2.flow_type from BC_COLLECTION_INFO t1 left join BC_SITE_INFO t2 on t1.site_id=t2.exp_no where t2.wwtp_id='"
				+ wwtp_id + "'";
		List<RowBase> rowmn = DataUtil.getSelectRows(sqlmn, parms);
		if (rowmn.size() > 0) {
			for (int i = 0; i < rowmn.size(); i++) {
				String parm = rowmn.get(i).getSafeString("DEVICE_NUM");
				if (rowmn.get(i).getSafeString("FLOW_TYPE").equals("0")) {
					mn_in = parm;
				}
				if (rowmn.get(i).getSafeString("FLOW_TYPE").equals("1")) {
					mn_out = parm;
				}
			}
		} else {
			return "{\"DataIn\":{},\"DataOut\":{}}\n";

		}
		HashMap<String, RowBase> ListIn = remoteService.getStationRealTimeData(mn_in);
		List<RowBase> ListIn_info = remoteService.getStationHouseData(mn_in);
		HashMap<String, RowBase> ListOut = remoteService.getStationRealTimeData(mn_out);
		List<RowBase> ListOut_info = remoteService.getStationHouseData(mn_out);
		JSONObject jsonObj = new JSONObject();
		jsonObj.put("DataIn_info", DataUtil.listToJSONObject(ListIn_info).toString());
		jsonObj.put("DataIn", JSONObject.fromObject(ListIn).toString());
		jsonObj.put("DataOut_info", DataUtil.listToJSONObject(ListOut_info).toString());
		jsonObj.put("DataOut", JSONObject.fromObject(ListOut).toString());
		return JSONUtils.toJSONString(jsonObj);
	}

	/**
	 * 排水管网 1.工厂列表 2.站点列表 3.参数字典表
	 */
	@RequestMapping("/getSplashInitData")
	@ResponseBody
	public String getSplashInitData() {
		// 默认数据
		JSONObject resultJson = new JSONObject();
		JSONArray factoryArray = new JSONArray();
		JSONArray stationArray = new JSONArray();
		JSONArray parameterArray = new JSONArray();
		resultJson.put("factoryList", factoryArray);
		resultJson.put("stationList", stationArray);
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String factorySql = "select t1.EXP_NO \"id\", t1.NAME \"name\",t1.mobile \"mobile\",t1.contact_address \"address\""
				+ ", t1.legal_person \"legalPerson\", t1.IMG \"imgUrl\",t2.S_DOMAINCAPTION \"unit\" "
				+ "from BC_ENTERPRISE_ATTR t1 left join(select s_id,S_DOMAINCAPTION from DATA_DEFULATDOMAIN where S_DOMAINNAME = '政区' and length(s_id)='2')t2 ON SUBSTR(t1.EXP_NO,3,2) = t2.s_id WHERE EXP_NO IS NOT NULL ORDER BY EXP_NO";
		List<RowBase> rows = DataUtil.getSelectRows(factorySql, parms);
		if (rows.size() > 0) {
			factoryArray = JSONArray.fromObject(rows);
			resultJson.put("factoryList", factoryArray);
		}
		String stationSql = "select t1.CODE \"id\",t1.NAME \"name\",t1.FLOW_TYPE \"flowType\""
				+ ",t1.STATION_CATEGORY \"stationCategory\",t1.WWTP_ID \"factoryId\",t2.S_DOMAINCAPTION \"unit\""
				+ ",t3.X \"x\",t3.Y \"y\",t1.x \"arcGisX\",t1.Y \"arcGisY\" from BC_SITE_INFO t1"
				+ " left join(select s_id,S_DOMAINCAPTION from DATA_DEFULATDOMAIN where S_DOMAINNAME = '政区' and length(s_id)='2')t2"
				+ " ON SUBSTR(t1.CODE,3,2) = t2.s_id"
				+ " left join STATION_INFO t3 on t3.STATION_CODE = t1.CODE";
		rows = DataUtil.getSelectRows(stationSql, parms);
		if (rows.size() > 0) {
			stationArray = JSONArray.fromObject(rows);
			resultJson.put("stationList", stationArray);
		}
		String parameterSql = "select S_DOMAINCODE \"id\",S_DOMAINCAPTION \"name\",s_unit \"unit\" from data_defulatdomain where S_DOMAINNAME='MONITOR_PARAM'";
		rows = DataUtil.getSelectRows(parameterSql, parms);
		if (rows.size() > 0) {
			parameterArray = JSONArray.fromObject(rows);
			resultJson.put("parameterList", parameterArray);
		}
		return resultJson.toString();
	}

	/**
	 * 排水管网 参数列表,包含报警上下线
	 */
	@RequestMapping("/getAlarmInfo")
	@ResponseBody
	public String getAlarmInfo() {
		JSONArray resultArray = new JSONArray();
		String sql = "select t1.name \"id\",t2.S_DOMAINCAPTION \"name\",t2.s_unit \"unit\",t1.SITE_ID \"stationId\",t1.DENSITY_ALARM_UPLIMIT \"alarmUp\" ,t1.DENSITY_ALARM_DOWMLIMIT \"alarmDown\" "
				+ "from BC_SITE_POLL t1 left join (select S_DOMAINCODE ,S_DOMAINCAPTION ,s_unit from data_defulatdomain where S_DOMAINNAME='MONITOR_PARAM')t2 on t2.S_DOMAINCODE = t1.NAME";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (rows.size() > 0) {
			resultArray = JSONArray.fromObject(rows);
		}
		return resultArray.toString();
	}

	/**
	 * 排水管网 主界面初始化 1.站点权限表 accessList 2.关注表 attentionList
	 */
	@RequestMapping("/getHomeInitData")
	@ResponseBody
	public String getHomeInitData(String userId) {
		// 默认数据
		JSONObject resultJson = new JSONObject();
		resultJson.put("accessList", new JSONArray());
		resultJson.put("attentionList", new JSONArray());
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("userId", userId));
		String accessSql = "select STATION_ID \"id\" from OPERATION_GROUP_STATION_ASSO t1,"
				+ "(SELECT GROUP_ID from OC_GROUP_ASSO WHERE USER_ID = @userId )t2 WHERE t1.OPERATION_GROUP_ID = t2.GROUP_ID";
		String attentionSql = "select STATION_CODE \"id\" from ATTENTION_INFO WHERE USER_ID = @userId";
		List<RowBase> rows = DataUtil.getSelectRows(accessSql, parms);
		if (rows.size() > 0) {
			JSONArray array = JSONArray.fromObject(rows);
			resultJson.put("accessList", array);
		}
		rows = DataUtil.getSelectRows(attentionSql, parms);
		if (rows.size() > 0) {
			JSONArray array = JSONArray.fromObject(rows);
			resultJson.put("attentionList", array);
		}
		return resultJson.toString();
	}

	/**
	 * 内窥检测 工程项目表
	 */
	@RequestMapping("/getEndoscopicProject")
	@ResponseBody
	public String getEndoscopicProject() {
		// 默认数据
		JSONArray resultArray = new JSONArray();
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select PROJECT_NO \"id\",PROJECT_NAME \"name\" from PROJECT";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (rows.size() > 0) {
			resultArray = JSONArray.fromObject(rows);
		}
		return resultArray.toString();
	}

	// 内窥检测 获取偏差精度统计列表-如果id为空，则获取全部数据
	@RequestMapping("/getEndoscopicPrecision")
	@ResponseBody
	public String getEndoscopicPrecision(String id) {
		if (dataModel == null) {
			dataModel = new DataModel();
		}
		if (id == null || id.isEmpty()) {
			// 如果为空，查询全不的数据
			return getEndoscopicAllPrecision();
		}
		JSONArray resultArray = new JSONArray();
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		// 获取工程名称
		parms.add(new GlobalParameter("id", id));
		String projectSql = "select PROJECT_NO,PROJECT_NAME from project where PROJECT_NO = @id ";
		List<RowBase> rows = DataUtil.getSelectRows(projectSql, parms);
		String projectName;
		if (rows != null && rows.size() > 0) {
			projectName = rows.get(0).getSafeString("PROJECT_NAME");
		} else {
			projectName = "";
		}
		// 开始查询偏差记录
		// 平面
		List<RowBase> resultList = new ArrayList<RowBase>();
		String diffSql = "select PROJECT_NO,STATISTICS_BOUND,WELL_NUM,SCALE,NOTE,NOTE_ALL ,DIFF_TYPE"
				+ " from ATTR_VIEW_POSITION_DIFF WHERE PROJECT_NO = '" + id + "' and DIFF_TYPE = 'panel'";

		rows = DataUtil.getSelectRows(diffSql, parms);
		if (rows == null || rows.size() == 0) {
			rows = dataModel.CalcPanelDiffFromDB(id);
		}
		if (rows != null) {
			resultList.addAll(rows);
		}
		// 高程
		diffSql = "select PROJECT_NO,STATISTICS_BOUND,WELL_NUM,SCALE,NOTE,NOTE_ALL ,DIFF_TYPE"
				+ " from ATTR_VIEW_POSITION_DIFF WHERE PROJECT_NO = '" + id + "' and DIFF_TYPE = 'height'";
		rows = DataUtil.getSelectRows(diffSql, parms);
		if (rows == null || rows.size() == 0) {
			rows = dataModel.CalcHeightDiffFromDB(id);
		}
		if (rows != null) {
			resultList.addAll(rows);
		}
		// 生成JSON
		JSONObject beanJson;
		for (RowBase rowBase : resultList) {
			beanJson = new JSONObject();
			beanJson.put("projectName", projectName);
			beanJson.put("name", rowBase.getSafeString("STATISTICS_BOUND"));
			beanJson.put("wellNum", rowBase.getSafeString("WELL_NUM"));
			beanJson.put("type", rowBase.getSafeString("DIFF_TYPE"));
			beanJson.put("percent", rowBase.getSafeString("SCALE"));
			beanJson.put("note", rowBase.getSafeString("NOTE"));
			beanJson.put("noteAll", rowBase.getSafeString("NOTE_ALL"));
			resultArray.add(beanJson);
		}
		return resultArray.toString();
	}

	// 内窥检测 1.全部偏差精度统计列表
	private String getEndoscopicAllPrecision() {
		JSONArray resultArray = new JSONArray();
		// key表示项目名，list表示项目该名下的精度偏差记录
		Map<String, List<RowBase>> resultMap = new HashMap<String, List<RowBase>>();
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String projectSql = "select PROJECT_NO,PROJECT_NAME from project";
		List<RowBase> rows = DataUtil.getSelectRows(projectSql, parms);
		List<RowBase> tempRows;
		List<RowBase> tempResultRows;
		String diffSql;
		String projectName;
		String projectId;
		if (rows != null && rows.size() > 0) {
			for (RowBase rowBase : rows) {
				projectId = rowBase.getSafeString("PROJECT_NO");
				projectName = rowBase.getSafeString("PROJECT_NAME");
				if (projectId.isEmpty()) {
					break;
				} else {
					// 缓存单个项目的统计记录
					tempResultRows = new ArrayList<>();
					resultMap.put(projectName, tempResultRows);
				}
				// 平面
				diffSql = "select PROJECT_NO,STATISTICS_BOUND,WELL_NUM,SCALE,NOTE,NOTE_ALL ,DIFF_TYPE"
						+ " from ATTR_VIEW_POSITION_DIFF WHERE PROJECT_NO = '" + projectId
						+ "' and DIFF_TYPE = 'panel'";
				tempRows = DataUtil.getSelectRows(diffSql, parms);
				if (tempRows == null || tempRows.size() == 0) {
					tempRows = dataModel.CalcPanelDiffFromDB(projectId);
				}
				if (tempRows != null) {
					tempResultRows.addAll(tempRows);
				}
				// 高程
				diffSql = "select PROJECT_NO,STATISTICS_BOUND,WELL_NUM,SCALE,NOTE,NOTE_ALL ,DIFF_TYPE"
						+ " from ATTR_VIEW_POSITION_DIFF WHERE PROJECT_NO = '" + projectId
						+ "' and DIFF_TYPE = 'height'";
				tempRows = DataUtil.getSelectRows(diffSql, parms);
				if (tempRows == null || tempRows.size() == 0) {
					tempRows = dataModel.CalcHeightDiffFromDB(projectId);
				}
				if (tempRows != null) {
					tempResultRows.addAll(tempRows);
				}
			}
			if (resultMap.size() > 0) {
				JSONObject beanJson;
				// 格式化Map
				Iterator<String> iterator = resultMap.keySet().iterator();
				while (iterator.hasNext()) {
					projectName = iterator.next();
					tempRows = resultMap.get(projectName);
					if (tempRows != null && tempRows.size() > 0) {
						for (RowBase rowBase : tempRows) {
							beanJson = new JSONObject();
							beanJson.put("projectName", projectName);
							beanJson.put("name", rowBase.getSafeString("STATISTICS_BOUND"));
							beanJson.put("wellNum", rowBase.getSafeString("WELL_NUM"));
							beanJson.put("type", rowBase.getSafeString("DIFF_TYPE"));
							beanJson.put("percent", rowBase.getSafeString("SCALE"));
							beanJson.put("note", rowBase.getSafeString("NOTE"));
							beanJson.put("noteAll", rowBase.getSafeString("NOTE_ALL"));
							resultArray.add(beanJson);
						}
					}
				}
			}
		}
		return resultArray.toString();
	}

	// 内窥检测 1.工程列表 2.管道列表
	@RequestMapping("/getEndoscopicProjectItem")
	@ResponseBody
	public String getEndoscopicProjectItem(String id) {
		// 默认数据
		JSONObject resultJson = new JSONObject();
		JSONArray itemArray = new JSONArray();
		JSONArray pipeArray = new JSONArray();
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String itemSql = "select ITEM_NO \"id\",PROJECT_NO \"fatherId\",ITEM_NAME \"name\",t2.S_DOMAINCAPTION \"unit\",DETECTION_LENGTH \"length\" from project_item t1 "
				+ "left join(select s_id,S_DOMAINCAPTION from DATA_DEFULATDOMAIN where S_DOMAINNAME = '政区' and length(s_id)='2') t2 on t2.s_id =t1.DISTRICT where 1 = 1";
		String pipeSql = "SELECT PIPE_NO \"id\",PIPE_SECTION_NAME \"name\",PROJECT_ITEM_NO \"fatherId\" FROM PIPE_SECTION ORDER BY PROJECT_ITEM_NO ASC";
		parms.add(new GlobalParameter("id", id));
		if (id != null && !id.isEmpty()) {
			itemSql += " and PROJECT_NO = @id";
		}
		List<RowBase> rows = DataUtil.getSelectRows(itemSql, parms);
		if (rows.size() > 0) {
			itemArray = JSONArray.fromObject(rows);
		}
		rows = DataUtil.getSelectRows(pipeSql, parms);
		if (rows.size() > 0) {
			pipeArray = JSONArray.fromObject(rows);
		}
		resultJson.put("projectItem", itemArray);
		resultJson.put("pipe", pipeArray);
		return resultJson.toString();
	}

	// 内窥检测 1.获取属性复核数据
	@RequestMapping("/getEndoscopicReview")
	@ResponseBody
	public String getEndoscopicReview(String pipeId) {
		JSONArray resultArray = new JSONArray();
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("pipeId", pipeId));
        String sql="SELECT ATTR_REVIEW_ID,NO,FLOW,PIPE_TYPE,PIPE_MATERIAL,PIPE_DIAMETER,SURVEYOR,WELL_NO_REVIEW"
        		+ ",WELL_NO_DESIGN,t2.S_DOMAINCAPTION PIPE_MATERIAL_REVIEW,t1.S_DOMAINCAPTION PIPE_MATERIAL_DESIGN"
        		+ ",PIPE_DIAMETER_VIEW,PIPE_DIAMETER_DESIGN,SECTION_SIZE_REVIEW,SECTION_SIZE_DESIGN,X_REVIEW,Y_REVIEW"
        		+ ",X_DESIGN,Y_DESIGN,PILE_TOP_REVIEW,PILE_TOP_DESIGN,PLANE_DIFF,HEIGHT_DIFF,ATTR_REVIEW_ADDRESS"
        		+ ",TO_CHAR(DETECTION_DATE,'yyyy:mm:dd hh24:mi:ss') as DETECTION_DATE from attr_review t"
        		+ " LEFT JOIN (select S_DOMAINCODE,S_DOMAINCAPTION from DATA_DEFULATDOMAIN where S_DOMAINNAME = 'PIPE_MATERIAL') t1"
        		+ " on t1.S_DOMAINCODE = t.PIPE_MATERIAL_DESIGN"
        		+ " LEFT JOIN (select S_DOMAINCODE,S_DOMAINCAPTION from DATA_DEFULATDOMAIN where S_DOMAINNAME = 'PIPE_MATERIAL') t2"
        		+ " on t2.S_DOMAINCODE = PIPE_MATERIAL_REVIEW where 1 = 1";
		if (pipeId != null && !pipeId.isEmpty()) {
			sql += " and PIPE_NO = @pipeId";
		}
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		JSONObject beanObject;
		JSONArray beanArray;
		JSONObject beanResultObject;
		if (rows.size() > 0) {
			for (RowBase bean : rows) {
				beanArray = new JSONArray();
				beanObject = new JSONObject();
				beanObject.put("designValue", toNotNullString(bean.getValue("WELL_NO_DESIGN")));
				beanObject.put("reviewValue", toNotNullString(bean.getValue("WELL_NO_REVIEW")));
				beanObject.put("valueName", "点号");
				beanArray.add(beanObject);
				beanObject = new JSONObject();
				beanObject.put("designValue", toNotNullString(bean.getValue("PIPE_MATERIAL_DESIGN")));
				beanObject.put("reviewValue", toNotNullString(bean.getValue("PIPE_MATERIAL_REVIEW")));
				beanObject.put("valueName", "材质");
				beanArray.add(beanObject);
				beanObject = new JSONObject();
				beanObject.put("designValue", toNotNullString(bean.getValue("PIPE_DIAMETER_DESIGN")));
				beanObject.put("reviewValue", toNotNullString(bean.getValue("PIPE_DIAMETER_VIEW")));
				beanObject.put("valueName", "管径");
				beanArray.add(beanObject);
				beanObject = new JSONObject();
				beanObject.put("designValue", toNotNullString(bean.getValue("SECTION_SIZE_DESIGN")));
				beanObject.put("reviewValue", toNotNullString(bean.getValue("SECTION_SIZE_REVIEW")));
				beanObject.put("valueName", "断面尺寸");
				beanArray.add(beanObject);
				beanObject = new JSONObject();
				beanObject.put("designValue", toNotNullString(bean.getValue("X_DESIGN")));
				beanObject.put("reviewValue", toNotNullString(bean.getValue("X_REVIEW")));
				beanObject.put("valueName", "X");
				beanArray.add(beanObject);
				beanObject = new JSONObject();
				beanObject.put("designValue", toNotNullString(bean.getValue("Y_DESIGN")));
				beanObject.put("reviewValue", toNotNullString(bean.getValue("Y_REVIEW")));
				beanObject.put("valueName", "Y");
				beanArray.add(beanObject);
				beanObject = new JSONObject();
				beanObject.put("designValue", toNotNullString(bean.getValue("PILE_TOP_DESIGN")));
				beanObject.put("reviewValue", toNotNullString(bean.getValue("PILE_TOP_REVIEW")));
				beanObject.put("valueName", "管底标高");
				beanArray.add(beanObject);
				beanResultObject = new JSONObject();
				beanResultObject.put("valueList", beanArray);
				beanResultObject.put("id", toNotNullString(bean.getValue("NO")));
				beanResultObject.put("pipeNo", toNotNullString(bean.getValue("PIPE_NO")));
				beanResultObject.put("flow", toNotNullString(bean.getValue("FLOW")));
				beanResultObject.put("pipeType", toNotNullString(bean.getValue("PIPE_TYPE")));
				beanResultObject.put("surveyor", toNotNullString(bean.getValue("SURVEYOR")));
				beanResultObject.put("dateTime", toNotNullString(bean.getValue("DETECTION_DATE")));
				beanResultObject.put("planeDiff", toNotNullString(bean.getValue("PLANE_DIFF")));
				beanResultObject.put("heightDiff", toNotNullString(bean.getValue("HEIGHT_DIFF")));
				beanResultObject.put("address", toNotNullString(bean.getValue("ATTR_REVIEW_ADDRESS")));
				resultArray.add(beanResultObject);
			}
		}
		return resultArray.toString();
	}

	// 内窥检测 缺陷列表
	@RequestMapping("/getEndoscopicDefect")
	@ResponseBody
	public String getEndoscopicDefect(String id) {
		// 默认数据
		JSONObject resultJson = new JSONObject();
		JSONArray levelArray = new JSONArray();
		JSONArray defectArray = new JSONArray();
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("id", id));
		String defectSql = "select t.FLOW \"flow\",t.PIPE_TYPE \"pipeType\",t.PIPE_DIAMETER \"pipeDiameter\" "
				+ ",t1.NAME \"pipeMaterial\",t.SURVEYOR \"surveyor\",TO_CHAR(t.DETECTION_DATE,'hh-mm-dd hh24:mi:ss') as \"dateTime\""
				+ ",t.START_WELL_NO \"startWellNo\",t.END_WELL_NO \"endWellNo\",t.DIRECTION \"direction\",t5.S_DOMAINCAPTION \"defectType\""
				+ ",t.DEFECT_LEVEL \"defectLevel\",t2.S_DOMAINCAPTION \"defectLevelDes\",t.RI \"ri\",t.RI_LEVEL \"riLevel\""
				+ ",t3.S_DOMAINCAPTION \"riLevelDes\",t.RI_SUGGESS \"riSuggest\",t.MI \"mi\",t.MI_LEVEL \"miLevel\""
				+ ",t4.S_DOMAINCAPTION \"miLevelDes\",t.MI_SUGGESS \"miSuggest\",t.LENGTH \"length\",t.LNO \"id\""
				+ " from NKJC_DEFECT t" + " LEFT JOIN PIPE_MATERIAL t1 on t1.PIPE_MATERIAL_ID = t.PIPE_MATERIAL"
				+ " LEFT JOIN (select * from DATA_DEFULATDOMAIN  where S_DOMAINNAME='DEFECT_LEVEL')t2 ON t2.S_DOMAINCODE = t.DEFECT_LEVEL"
				+ " LEFT JOIN (select * from DATA_DEFULATDOMAIN  where S_DOMAINNAME='RI_LEVEL')t3 ON t3.S_DOMAINCODE = t.RI_LEVEL"
				+ " LEFT JOIN (select * from DATA_DEFULATDOMAIN  where S_DOMAINNAME='MI_LEVEL')t4 ON t4.S_DOMAINCODE = t.MI_LEVEL"
				+ " LEFT JOIN (select * from DATA_DEFULATDOMAIN  where S_DOMAINNAME='DEFECT_TYPE')t5 ON t5.S_DOMAINCODE = t.DEFECT_TYPE OR t5.S_DOMAINCAPTION = t.DEFECT_TYPE"
				+ " where 1 = 1";
		String levelSql = "select S_DOMAINCODE,S_DOMAINCAPTION,S_DOMAINNAME from DATA_DEFULATDOMAIN WHERE S_DOMAINCODE = '4' AND (S_DOMAINNAME='DEFECT_LEVEL' OR S_DOMAINNAME='RI_LEVEL' OR S_DOMAINNAME='MI_LEVEL')";
		// 默认的等级描述
		List<RowBase> rows = DataUtil.getSelectRows(levelSql, parms);
		if (rows != null && rows.size() > 0) {
			levelArray = JSONArray.fromObject(rows);
		}
		if (id != null && !id.isEmpty()) {
			defectSql += " and t.PIPE_NO = @id";
		}
		rows = DataUtil.getSelectRows(defectSql, parms);
		if (rows != null && rows.size() > 0) {
			defectArray = JSONArray.fromObject(rows);
		}
		resultJson.put("levelList", levelArray);
		resultJson.put("defectList", defectArray);
		return resultJson.toString();
	}

	private Object toNotNullString(Object text) {
		if (text == null) {
			return "";
		} else {
			return text.toString();
		}
	}

	private String tableSave(String tableName, RowBase entity) throws Exception {
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable(tableName);

		String returnString = FormEditInterceptorClass.BeforeDbEditOptionEx(pTable, entity, DbEditType.Insert);
		if (StringUtils.IsNullOrSpace(returnString)) {
			pTable.startEditing();
			try {
				pTable.save(entity);

				// 再做一次字典转议
				ConvertCaptionUtils.convertCaptionField(pTable, entity);
				ObjectMapper mapper = new ObjectMapper();
				String userMapJson = mapper.writeValueAsString(entity);
				pTable.stopEditing(true);

				return userMapJson;
			} catch (Exception e) {
				pTable.stopEditing(false);
				throw e;
			}
		} else {
			return returnString;
		}
	}

	private void checkToken(String token) {
		if (!ObjectUtils.equals(token, this.token)) {
			throw new RuntimeException("认证错误！");
		}
	}

	// 获取污水项目的Arcgis坐标
	@RequestMapping("/getArcgisXYforWS")
	@ResponseBody
	private String getArcgisXYforWS() {
		JSONArray resultArray = new JSONArray();
		String sql = "select t1.s_domaincaption,t.name,t.flow_type,t.x,t.y,t2.x as bdx,t2.y as bdy,t.exp_no,t.wwtp_id from BC_SITE_INFO t "
				+ " left join (select S_DOMAINCODE,S_DOMAINNAME,s_domaincaption,PY_PREFIX, s_id,s_pid ,TYPE from DATA_DEFULATDOMAIN  "
				+ " where length(s_id)='2' and S_DOMAINNAME ='政区') t1 on t1.s_id = SUBSTR(t.exp_no,3,2) "
				+ " left join station_info t2 on t2.station_code = t.exp_no "
				+ " where t.station_category='0'and flow_type = '1'";
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		if (rows.size() > 0) {
			resultArray = JSONArray.fromObject(rows);
		}
		return resultArray.toString().replace("出水口","污水厂");
	}
}
